﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product;
using ERP_RORZE.Product.BusOder;
using ERP_RORZE_DLL.Product.database;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_SYSTEM;

namespace ERP_RORZE.Product.UserControls
{
    public partial class ctrlBusOderList : DevExpress.XtraEditors.XtraUserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        //
        ClsBusOderDAO clsBusOderDAO = new ClsBusOderDAO();
        DataTable dt;
        ClsBusOderTable busoder = new ClsBusOderTable();

        public ctrlBusOderList()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);
        }
        void Re_load(object sender, EventArgs e)
        {
            dt = clsBusOderDAO.GetAll();
            dgvBusOderList.DataSource = dt;
        }
        private void ctrlBusOderList_Load(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            busoder.BOderNo = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "BOderNo").ToString();
            clsBusOderDAO.Delete(busoder);
            Re_load(sender, e);
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            Frm_BusOderAdd frmAdd = new Frm_BusOderAdd();
            frmAdd.Re_Load += new EventHandler(Re_load);
            frmAdd.Show();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            Frm_BusOderEdit frmEdit = new Frm_BusOderEdit();
            frmEdit.Re_Load += new EventHandler(Re_load);
            frmEdit.BOderNo = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "BOderNo").ToString();
            frmEdit.BlastQty = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "BlastQty").ToString();
            frmEdit.Change = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Change").ToString();
            frmEdit.ChangeDate = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "ChangeDate").ToString(); 
            frmEdit.date = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "date").ToString();
            frmEdit.Dealine = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Deadline").ToString();
            frmEdit.Finished = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Finished").ToString();
            frmEdit.HelisertQty = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "HelisertQty").ToString();
            frmEdit.Imported = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Imported").ToString();
            frmEdit.ImportFrom = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "ImportFrom").ToString();
            frmEdit.MONo = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "MONo").ToString();
            frmEdit.MOQty = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "MOQty").ToString();
            frmEdit.Note = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Note").ToString();
            frmEdit.PartID = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "PartID").ToString();
            frmEdit.planNo = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "planNo").ToString();
            frmEdit.Qty = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Qty").ToString();
            frmEdit.RawQty = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "RawQty").ToString();
            frmEdit.Started = gridViewBusOder.GetRowCellValue(gridViewBusOder.FocusedRowHandle, "Started").ToString();
            frmEdit.Show();
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            DataSet as400 = new DataSet();
            DataSet staff = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable staff_db2;
            DataTable staff_local;
            try
            {
                //Get Data From AS400                
                masterConnection.Open();
                sql = "select * from openquery(AS400_MPFLIB, 'Select * from MPOR00 Where ORSICD=''B02420''')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                staff_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [242_BusOder]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(staff, "242_BusOder");
                staff_local = staff.Tables["242_BusOder"];
                                
                foreach (DataRow as400_row in staff_db2.Rows)
                {
                    DataRow[] current_row = staff_local.Select("BOderNo='" + as400_row["ORODNO"].ToString() + "'");
                    if (current_row.Length > 0)
                    {
                        foreach (DataRow local_row in current_row)
                        {
                            local_row.BeginEdit();
                            local_row["PlanNo"] = as400_row["KOKENO"].ToString();
                            local_row["PartID"] = as400_row["ORHNBN"].ToString();
                            local_row["Qty"] = as400_row["ORHTSU"].ToString();                            
                            local_row.EndEdit();
                        }
                    }
                    else
                    {
                        DataRow new_row = staff_local.NewRow();
                        new_row["BOderNo"] = as400_row["ORODNO"].ToString();
                        new_row["PlanNo"] = as400_row["KOKENO"].ToString();
                        new_row["PartID"] = as400_row["ORHNBN"].ToString();
                        new_row["Qty"] = as400_row["ORHTSU"].ToString();                        
                        staff_local.Rows.Add(new_row);
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(staff, "242_BusOder");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=4";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                Re_load(sender, e);

                MessageBox.Show("Database has been updated successfull.");
                                
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            btnAddNew_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            btnEdit_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }
    }
}
